package org.mics.lang.file;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.enmus.ExcelType;
import org.jeecgframework.poi.excel.entity.params.ExcelExportEntity;

/**
 * excel工具类 
 * @author mics
 * @date 2018年7月3日
 * @version 1.0
 */
public class ExcelUtils {
	/**
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * @param file 读取数据的源Excel
	 * @param ignoreRows 读取数据忽略的行数，比如行头不需要读入 忽略的行数为1
	 * @return 读出的Excel中数据的内容
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String[][] getData(InputStream inputStream,  int startRow){
		Sheet st = getSheet(inputStream);
		List<String[]> result  =new ArrayList<String[]>();
		int rowSize=0;
		for (int rowIndex = startRow; rowIndex <= st.getLastRowNum(); rowIndex++) {
			rowSize=rowSize+1;
			Row row = st.getRow(rowIndex);
			if (row == null) {
				continue;
			}
			String[] values = new String[row.getLastCellNum()];
			Arrays.fill(values, "");
			boolean hasValue = false;
			for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
				Cell cell  = row.getCell(columnIndex);
				String value="";
				if(cell != null) {
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case Cell.CELL_TYPE_NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							Date date = cell.getDateCellValue();
							if (date != null) {
								value = new SimpleDateFormat("yyyy-MM-dd").format(date);
							} else {
								value = "";
							}
						} else {
							value = new DecimalFormat("0").format(cell.getNumericCellValue());
						}
						break;
					case Cell.CELL_TYPE_FORMULA:
						// 导入时如果为公式生成的数据则无值
						if (StringUtils.isNotBlank(cell.getStringCellValue())) {
							value = cell.getStringCellValue();
						} else {
							value = cell.getNumericCellValue() + "";
						}
						break;
					case Cell.CELL_TYPE_BLANK:
						value="";
						break;
					case Cell.CELL_TYPE_ERROR:
						value = "";
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						value = (cell.getBooleanCellValue() == true ? "Y" : "N");
						break;
					default:
						value = "";
					}
				}
				values[columnIndex] =value;
				if(StringUtils.isNotBlank(value)){
					hasValue = true;
				}
			}
			if (hasValue) {
				result.add(values);
			}
		}
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}
	
	/**
	 * 根据文件流读取sheet
	 * @author mics
	 * @date 2022-8-19
	 * @version  1.0
	 * @param inputStream 文件流
	 * @return sheet
	 */
	private static Sheet  getSheet(InputStream inputStream){
		if(inputStream == null){
			throw new IllegalArgumentException("读取文件错误，文件不能为空！");
		}
		BufferedInputStream in=null;
		Sheet st=null;
		try{
			in = new BufferedInputStream(inputStream);
			Workbook wb = WorkbookFactory.create(in);
			st = wb.getSheetAt(0);
			return st;
		}catch(Exception exception){
			throw new ExcelReadWriteException("读取文件错误!",exception);
		}finally {
			try {
				in.close();
			} catch (IOException e) {
				throw new ExcelReadWriteException("关闭流异常",e);
			}
		}
	}

	/**
	 * 简单的自定义需要导出的数据列
	 * @param datas 数据集合
	 * @param title 表格标题(使用英文,连接)
	 * @param key  表格列名(key)(使用英文,连接)
	 * @param name 导出excle的名称(最好不要使用特殊符号)
	 * @param response
	 */
	public static void exportData( List<Map<String,Object>> datas,String title, String key, String header,String name, HttpServletResponse response){
		if(datas == null || datas.size()== 0){
			throw new IllegalArgumentException("导出表格数据不能为空！");
		}
		if(StringUtils.isBlank(key)){
			throw new IllegalArgumentException("表格标题不能为空！");
		}
		if( StringUtils.isBlank(title)){
			throw new IllegalArgumentException("表格列名不能为空！");
		}
        String [] titles = title.split(",");
        
        String [] keys = key.split(",");
        
        List<ExcelExportEntity> entitys = new ArrayList<ExcelExportEntity>();
        for (int i = 0; i < titles.length; i++) {
        	entitys.add(new ExcelExportEntity(titles[i], keys[i]));
		}
	    Workbook workbook =  ExcelExportUtil.exportExcel(new ExportParams(header, name,ExcelType.XSSF), entitys, datas);
	    response.setContentType("application/x-download");  
	    //设置导出文件名称  
	    try {
			response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(name, "UTF-8")+".xls");
			OutputStream os = response.getOutputStream();
			workbook.write(os);
			os.close();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 导出指定路径excel
	 * @author mics
	 * @date 2022-8-19
	 * @version  1.0
	 * @param response 响应流
	 * @param request 请求流
	 * @param excelPath 文件存储路径 
	 * @param fileName 文件名称
	 */
	public void downloadExcel(HttpServletResponse response,HttpServletRequest request,String excelPath,String fileName) {
        try {
            //获取文件的路径
        	 File path =   new File(this.getClass().getClassLoader().getResource("classpath:").getPath());  
            // 读到流中
            InputStream inStream = new FileInputStream(path+excelPath);
            // 设置输出的格式
            response.reset();
            response.setContentType("bin");
            response.addHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            // 循环取出流中的数据
            byte[] b = new byte[2048];
            int len;

            while ((len = inStream.read(b)) > 0){
                response.getOutputStream().write(b, 0, len);
            }
            inStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
